package com.hzt.agprosaleplatform.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.hzt.agprosaleplatform.entity.Good;
import com.hzt.agprosaleplatform.entity.GoodsCategory;
import com.hzt.agprosaleplatform.entity.GoodsEvaluation;
import com.hzt.agprosaleplatform.entity.detailedGoods;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Update;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;

import java.util.List;
import java.util.Map;

/**
 * 关于商品的SQL方法
 */
@Mapper
public interface GoodsMapper extends BaseMapper<Good> {
    //传入品类查询前10条数据返回
    @Select("SELECT * FROM t_goods WHERE category = #{category} AND isDeleted = 0 LIMIT 15")
    List<Good> selectByCategory(String category);

    @Select("SELECT * FROM t_goods g " +
    "LEFT JOIN t_goods_category c ON c.code = g.category " +
    "WHERE (c.category_name = #{category} OR g.category LIKE CONCAT((SELECT code FROM t_goods_category WHERE category_name = #{category} LIMIT 1), '%')) " +
    "AND g.isDeleted = 0")
    IPage<Good> pageForCategory(IPage<Good> page, @Param("category") String category);



    //多表查询商品详细
    @Select("SELECT g.gid, g.shop_id as shopId, s.shopName, s.shopAvatar, g.title, " +
            "g.g_price as price, g.tags as tagsStr, g.province , g.city, g.stock_quantity, " +
            "CAST(GROUP_CONCAT(gi.imageUrl) AS CHAR) as goodsImgStr " +
            "FROM t_goods g " +
            "LEFT JOIN t_shop s ON g.shop_id = s.shopId " +
            "LEFT JOIN t_goods_image gi ON g.gid = gi.gid " +
            "WHERE g.gid = #{gid} AND g.isDeleted = 0 " +
            "GROUP BY g.gid, g.shop_id, s.shopName, s.shopAvatar, g.title, " +
            "g.g_price, g.tags, g.province, g.stock_quantity")
    detailedGoods selectDetailedGoodsByGId(@Param("gid") Integer gid);


    //品类查询自定义方法
    @Select("SELECT * FROM t_goods_category")
    List<GoodsCategory> selectAllCategories();

    @Select("SELECT * FROM t_goods_category WHERE parent_code = #{parentCode}")
    List<GoodsCategory> selectByParentCode(String parentCode);

        @Insert("INSERT INTO t_goods_image (gid, imageUrl) VALUES (#{gid}, #{imageUrl})")
    int insertGoodsImage(@Param("gid") Integer gid, @Param("imageUrl") String imageUrl);

        @Delete("DELETE FROM t_goods_image WHERE gid = #{gid}")
    int deleteGoodsImages(@Param("gid") Integer gid);

    @Select("SELECT imageUrl FROM t_goods_image WHERE gid = #{gid}")
    List<String> selectGoodsImagesByGid(@Param("gid") Integer gid);

    @Insert("INSERT INTO t_goods_evaluation (uid, gid, evaluation, stars, send_date) " +
            "VALUES (#{uid}, #{gid}, #{evaluation}, #{stars}, #{send_date})")
    int evaluateProduct(GoodsEvaluation goodsEvaluation);

        @Select("SELECT e.eva_id, e.uid, u.username as userName, u.avatar as avatarImg, " +
            "e.gid, e.evaluation, e.stars, e.send_date " +
            "FROM t_goods_evaluation e " +
            "LEFT JOIN t_user u ON e.uid = u.uid " +
            "WHERE e.gid = #{gid} " +
            "ORDER BY e.send_date DESC")
    List<GoodsEvaluation> getProductEvaluate(@Param("gid") Integer gid);

    @Delete("DELETE FROM t_goods_evaluation WHERE gid = #{gid}")
    int deleteGoodsEvaluations(@Param("gid") Integer gid);

    @Select("SELECT COUNT(*) FROM order_products WHERE gid = #{gid}")
    int countOrderItemsByGid(@Param("gid") Integer gid);

    @Delete("DELETE FROM order_products WHERE gid = #{gid}")
    int deleteOrderItemsByGid(@Param("gid") Integer gid);

    // 获取店铺商品销量排行
    @Select("SELECT title as name, CAST(turnover AS UNSIGNED) as value FROM t_goods WHERE shop_id = #{shopId} ORDER BY CAST(turnover AS UNSIGNED) DESC LIMIT 10")
    List<Map<String, Object>> selectGoodsSalesRanking(@Param("shopId") Integer shopId);
}
